﻿use master
go
if exists(select * from sys.databases where name='RFID')
	drop database RFID
go
create database RFID
go
use RFID
go

create table GoodsInfo
(
	Id int primary key identity(1,1),
	[Name] nvarchar(50) not null,
	Price money check(Price>0) not null
)

insert into GoodsInfo values(N'鸡腿饭',11)
insert into GoodsInfo values(N'鸭腿饭',13)
insert into GoodsInfo values(N'烤肉饭',11)
insert into GoodsInfo values(N'酸辣米线',8)
insert into GoodsInfo values(N'鸡丁米线',10)
insert into GoodsInfo values(N'青菜面条',7)
insert into GoodsInfo values(N'鸡蛋灌饼',5)
select * from GoodsInfo

create table StuInfo
(
	Id int primary key identity(1001,1),
	[Name] nvarchar(12) not null,
	Tell varchar(11) not null,
	[Pwd] varchar(16) not null default('123456')
)

insert into StuInfo values(N'张佚名','13812345678','123456')
insert into StuInfo values(N'赵四','13812345679','123456')
insert into StuInfo values(N'赵敏','13812345610','123456')
insert into StuInfo values(N'王强','13812345611','123456')
insert into StuInfo values(N'王鹏','13812345615','123456')
insert into StuInfo values(N'李明','13812345620','123456')
select * from StuInfo

create table CardInfo
(
	Id varchar(24) primary key not null,
	[Sid] int references StuInfo(Id) not null,
	[Pwd] varchar(16) not null default('123456'),
	[Disabled] bit default(0) not null,
	Rich money check(Rich>=0) not null,
	IsDelete bit default(0) not null
)
select * from CardInfo

create table RichInfo
(
	Id int primary key identity(1001,1),
	RId varchar(24) references CardInfo(Id) not null,
	Rich money check(Rich>0) not null,
	[Date] datetime default(getdate()) not null
)

create table OrderInfo
(
	Id int primary key identity(1001,1),
	RId varchar(24) references CardInfo(Id) not null,
	[Date] datetime default(getdate()) not null
)

create table OrderDetial
(
	OId int references OrderInfo(Id) not null,
	GId int references GoodsInfo(Id) not null,
	Price money check(Price>0) not null,
	[Count] int check(count>0) not null,
	primary key(OId,GId)
)

create table WorkInfo
(
	Id int primary key identity(1001,1),
	RId varchar(24) references CardInfo(Id) not null,
	[Date] datetime default(getdate()) not null,
	Flag bit default(0) not null
)

go
create proc ChangeCard @old varchar(24), @new varchar(24)
as
	declare @Sid int,@Pwd varchar(16),@Disabled bit,@Rich money,@IsDelete bit,@error int
	select @Sid=[sid],@Pwd=Pwd,@Disabled=[Disabled],@Rich=Rich,@IsDelete=IsDelete from CardInfo where Id=@old
	begin tran
		set @error = 0
		insert into CardInfo values(@new,@Sid,@Pwd,@Disabled,@Rich,@IsDelete)
		set @error += @@ERROR
		update RichInfo set RId=@new where RId=@old
		set @error += @@ERROR
		update OrderInfo set RId=@new where RId=@old
		set @error += @@ERROR
		update WorkInfo set RId=@new where RId=@old
		set @error += @@ERROR
		delete from WorkInfo where RId=@old
		set @error += @@ERROR
	if @error=0
		commit tran
	else
		rollback tran
go